# -*- coding: utf-8 -*-
'''
@Time    : 2021/4/26 23:12
@Author  : xiaojie
@File    : HomeWork16.py
'''

import pymysql

'''
创建表
'''


def create_table():
    db = pymysql.connect(host='49.233.39.160', port=3306, database='lebo16', user='user', password='leboAa!#$123',
                         charset='utf8')
    cursor = db.cursor()

    cursor.execute("drop table if EXISTS students_0922")
    sql = """create table students_0922(
    studentNo int unsigned primary key auto_increment,
    name varchar(20),
    sex varchar(2),
    hometown varchar(40),
    age int unsigned,
    class varchar(10),
    card varchar(20)
    )"""
    try:
        cursor.execute(sql)
        print("数据表创建成功")
    except Exception as e:
        print("数据表创建失败，原因：%s" % e)
    finally:
        db.close()


# 插入数据到表
def insert_table():
    db = pymysql.connect(host='49.233.39.160', port=3306, database='lebo16', user='user', password='leboAa!#$123',
                         charset='utf8')
    cursor = db.cursor()
    sql = '''
    insert into students_0922 values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655')    
    '''
    count = cursor.execute(sql)
    try:
        # 提交插入语句&执行
        db.commit()
        print("新增数据%d条数据成功" % count)
    except Exception as e:
        print("数据表创建失败，原因：%s" % e)
    finally:
        # 关闭Cursor对象
        cursor.close()
        # 关闭Connection对象
        db.connect()


from pymysql import *
'''
查询数据
'''
def select_table():
    # 创建Connection连接
    conn = connect(host='49.233.39.160', port=3306, database='lebo16', user='user', password='leboAa!#$123', charset='utf8')
    print(conn)
    # 获得Cursor对象
    cs1 = conn.cursor()
    # 查询students表数据
    count = cs1.execute('select * from students_0922')
    print('查询students_0922表一共有%d条数据' % count)

    # 提交之前的操作，如果之前已经之执行过多次的execute，那么就都进行提交
    conn.commit()

    # 关闭Cursor对象
    cs1.close()
    # 关闭Connection对象
    conn.close()

create_table()
insert_table()
select_table()
